package SQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCbook {
	  private static final String userName = "root";
	    private static final String password = "123456";
	    private static final String DRIVER = "com.mysql.jdbc.Driver";
	    private static String strSql;
	    public static Connection con;
	    public static PreparedStatement pStatement;
	    public static ResultSet rs;
	    private static String url = "jdbc:mysql://localhost:3306/book?useSSL=true&characterEncoding=utf-8";

	    public static void registerDriver() {
	        try {
	            Class.forName(DRIVER);
	            
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	            System.out.println("未注册成功");
	        }
	    }

	   public static void insert(int num,String name) throws SQLException {//插入操作
		   strSql = "insert into books(num,name) values(?,?)";
	       pStatement = con.prepareStatement(strSql);	    	   
	    	   pStatement.setInt(1,num);
	           pStatement.setString(2,name);
	           pStatement.executeUpdate();    
	   } 
	   public static void query() throws SQLException {//查询操作
		   strSql = "select * from books";
	       pStatement = con.prepareStatement(strSql);
	       rs = pStatement.executeQuery();
		   System.out.println("您要查询的所有图书如下所示：");
	       while(rs.next()) {
	    	   System.out.print("您要查询的书号为 "+rs.getInt(1));
	    	   System.out.println(",书名为"+rs.getString(2));
	       }
	   }
	   public static void query(int num) throws SQLException {//书号查询操作
		   strSql = "select * from books";
	       pStatement = con.prepareStatement(strSql);
	       rs = pStatement.executeQuery();
	       boolean querynumflag=true;
		   System.out.println("您要查询的所有图书如下所示：");
	       while(rs.next()) {
	    	   int num1 = rs.getInt(1);
	    	   if(num1==num) {
	    		   System.out.print("您要查询的书号为 "+rs.getInt(1));
		    	   System.out.println(",书名为"+rs.getString(2));
	    	   }else {
	    		   querynumflag=false;
	    	   }  	   
	       }
	       if(querynumflag) {
	    	   System.out.println("查询成功！");
	       }else {
	    	   System.out.println("查询失败，暂无此书！");
	       }
	   }
	   public static void query(String name) throws SQLException {//书名查询操作
		   strSql = "select * from books";
	       pStatement = con.prepareStatement(strSql);
	       rs = pStatement.executeQuery();
	       boolean querynameflag = true;
		   System.out.println("您要查询的所有图书如下所示：");
		   while(rs.next()) {
	    	   String name1 = rs.getString(1);
	    	   if(name.equals(name1)) {
	    		   System.out.print("您要查询的书号为 "+rs.getInt(1));
		    	   System.out.println(",书名为"+rs.getString(2));
	    	   }else {
	    		   querynameflag=false;
	    	   }  	   
	       }
	       if(querynameflag) {
	    	   System.out.println("查询成功！");
	       }else {
	    	   System.out.println("查询失败，暂无此书！");
	       }
	   }
	   
	   public static void delete(int num) throws SQLException {
		   strSql = "delete from books where num=?";
		   pStatement = con.prepareStatement(strSql);
		   pStatement.setInt(1, num);
		   pStatement.executeUpdate();
		   System.out.println("删除成功！");
	   }   
	   public static void delete(String name) throws SQLException {
		   strSql = "delete from books where name=?";
		   pStatement = con.prepareStatement(strSql);
		   pStatement.setString(1, name);
		   pStatement.executeUpdate();
		   System.out.println("删除成功！");
	   }
	   public static void update(int num,String password) throws SQLException {//更新操作
		   strSql = "update books set name=? where num=?";
		   pStatement = con.prepareStatement(strSql);
		   	   pStatement.setInt(2,num);
			   pStatement.setString(1, password);
			   pStatement.executeUpdate();
			   System.out.println("书名修改成功！");		  
	   }

	    public static Connection getConnection() throws SQLException {
	        Connection conn = null;
	        System.out.println("连接数据库 ...");
	        conn = DriverManager.getConnection(url, userName, password);
	        System.out.println("连接数据库成功!");
	        return conn;
	    }

	    public static void realeaseAll(ResultSet rs, PreparedStatement st,
	            Connection conn) {
	        if (rs != null) {
	            try {
	                rs.close();
	                // rs = null;
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        if (st != null) {
	            try {
	                st.close();
	                // st = null;
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        if (conn != null){
	            try {
	                conn.close();
	                // conn = null;
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	    }
	   /* public static void main(String[] args) {
	        
	        registerDriver();
	        try {
	            con = getConnection();
	        } catch (SQLException e) {

	            e.printStackTrace();
	        }
	        
	        try {           
	            //insert("ccc","bbb");      
	           //delete("ccc");
	           //update();
	           //System.out.println(query("aaa","bbb"));
	        } catch (Exception e) {
	            e.printStackTrace();
	        } finally {
	        	realeaseAll(rs, pStatement, con);
	        }
	    }*/
}
